import pymysql
import openpyxl

db = pymysql.connect(host='10.0.0.133',
                     port=3306,
                     user='root',
                     password='123456',
                     database='text1',
                     charset='utf8')
wb = openpyxl.load_workbook('baidu.xlsx')
excel_sheet = wb.active
sheet = wb['Sheet1']
#创建游标
# 使⽤ cursor() ⽅法创建⼀个游标对象 cursor
cursor = db.cursor()
# 使⽤ execute() ⽅法执⾏ SQL 查询
sql = ("insert into user_info ("
       "id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,company) "
       "values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)")
params = []

for row in sheet.iter_rows(2,65535):
    id = row[0].value
    idcard = row[1].value
    username = row[2].value
    realname = row[3].value
    pwd = row[4].value
    telphone = row[5].value
    email = row[6].value
    age = row[7].value
    sex = row[8].value
    address = row[9].value
    hiredate = row[10].value
    sal = row[11].value
    job = row[12].value
    company = row[13].value
    params.append((id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,company))
cursor.executemany(sql, params)

# 提交
db.commit()

# 6.关闭资源
cursor.close()
db.close()
